# coding=utf-8
__author__ = 'zlite'
import os
import pymssql
from datetime import datetime, timedelta
from email.mime.application import MIMEApplication
from email import MIMEText
import email
from email.mime.multipart import MIMEMultipart
import smtplib
import xlsxwriter
import sys
reload(sys)
sys.setdefaultencoding('utf8')
os.environ['TDSVER'] ='7.3'


SQL='''SELECT J.CityID 城市编号
          ,J.CITYNAME 城市名称
          ,replace(A.EB_NAME,',','') 主大客户名
          ,replace(A.EB_NameDetail,',','') 大客户名
    ,A.CustomerID 用户ID
    ,A.Phone 主号
    ,C.Cellphone 子号
    ,case when C.ContactPhone is null then C.Cellphone else C.ContactPhone end 联系电话 
    ,replace(A.EB_NameDetail,',','') 主号名称
          ,C.OrderID 订单编号
    ,C.OrderTime 下单日期
 ,CONVERT(varchar(100), c.DataDate, 23)  报单日期
    ,C.UCODE 司机编号
    ,H.CarCode 车牌
    ,D.OrderMoney 订单金额
    ,D.WaitMoney 等待费
    ,D.MileageMoney 里程费
    ,D.PMoney 信息费
    ,E.WaitTime 等待时长
    ,F.Mileage 里程
    ,convert(varchar(100),E.BeginDriveTime,120) 开始代驾时间
    ,CONVERT(varchar(100),E.EndDriveTime,120)结束代驾时间
    ,replace(F.BeginDriveAddress,',','') 开始代驾地址
    ,replace(F.EndDriveAddress,',','') 结束代驾地址
    ,G.SpecialField 特殊字段
 ,replace(replace(G.Remark,',','|'),' ','|') 客服备注
    FROM ADJBI_SOURCE.dbo.Out_Excel_EB_User A   
   INNER JOIN DW_ORDER_INFO C
      ON A.CustomerID=C.EB_CustomerID
     AND C.State=30
   INNER JOIN ADJBI_SOURCE.dbo.D_CallCenterOrderInfo G
     ON C.OrderID=G.OrderId
  INNER JOIN DW_ORDER_AMOUNT D
     ON C.OrderID=D.OrderID
  INNER JOIN DW_ORDER_LOCATION F
     ON C.OrderID=F.OrderID
  INNER JOIN ADJBI_SOURCE.dbo.D_OrderDetail H
     ON C.OrderID=H.OrderId
   INNER JOIN DIM_CITY J
     ON C.CityID=J.RegionID
  INNER JOIN DW_ORDER_TIME E
     ON C.OrderID=E.OrderID
    AND E.ReportTime>='#begin_date#'
    AND E.ReportTime< '#end_date#'
  WHERE A.EB_Name='#customer#'
'''

zjtz_sql = '''

select m.create_time 扣款时间 
     , m.OrderId 订单编号
   ,l.Phone 主号
   ,l.EB_NameDetail 主号名称
   ,n.OrderMoney 订单金额
   ,m.Amount 账户金额变动
   ,case when m.ConsumType=103 then '客户资金调配【扣款】' else '客户资金调配【返款】' end 客户资金调配
   ,m.Remark 消费描述
   ,o.Content "调配原因中间有******为有多个调配记录"
   ,a.SpecialField 特殊字段
    ,C.OrderTime 下单日期
    ,C.UCODE 司机编号
    ,H.CarCode 车牌
    ,n.WaitMoney 等待费
    ,n.MileageMoney 里程费
    ,n.PMoney 信息费
    ,D.WaitTime 等待时长
    ,F.Mileage 里程
    ,D.BeginDriveTime 开始代驾时间
    ,D.EndDriveTime 结束代驾时间
    ,replace(F.BeginDriveAddress,',','') 开始代驾地址
    ,replace(F.EndDriveAddress,',','') 结束代驾地址
  from ADJBI_SOURCE.[dbo].D_CustomerAmountDetail m
 INNER JOIN DW_ORDER_AMOUNT n
    ON M.OrderId=N.OrderID
 INNER JOIN ADJBI_SOURCE.dbo.Out_Excel_EB_User l
    ON m.CustomerId=l.CustomerID
  LEFT JOIN (SELECT Orderid
                   ,Content=stuff((select '******'+Content 
               from ADJBI_SOURCE.[dbo].Cwo_WorkOrder t 
          where Orderid=ADJBI_SOURCE.[dbo].Cwo_WorkOrder.Orderid 
            for xml path('')), 1, 6, '') 
               FROM ADJBI_SOURCE.[dbo].Cwo_WorkOrder
     GROUP BY Orderid) o
    ON m.OrderId=o.OrderId
  LEFT JOIN ADJBI_SOURCE.dbo.D_CallCenterOrderInfo A
    ON m.OrderId=A.OrderID
 INNER JOIN DW_ORDER_INFO C
    ON M.OrderID=C.OrderID
  left JOIN ADJBI_SOURCE.dbo.D_OrderDetail H
    ON M.OrderId=H.OrderID
 INNER JOIN DW_ORDER_TIME D
    ON M.OrderID=D.OrderID
 INNER JOIN DW_ORDER_LOCATION F
    ON M.OrderID=F.OrderID
 where consumtype in (103,104) 
   and m.create_time>='#begin_date#'
   and m.create_time< '#end_date#'
 order by m.create_time,m.orderid

'''


day = str(datetime.now() - timedelta(days=0)).replace('-', '').split()[0]
host = "114.141.132.127:7899"
user = "app_bi_r"
pwd = "b80Dea@73c92"
db = "ADJBIDW"
conn = pymssql.connect(host=host, user=user, password=pwd, database=db, charset="utf8")
cur = conn.cursor()
if not cur:
    print "连接数据库失败"
else:

    file_name = u'D:\工作相关\大客户数据\凹凸租车%s.xlsx' % day
    workbook = xlsxwriter.Workbook(file_name)
    header_style = workbook.add_format({'bold': True})
    header_style.set_bg_color('#4F81BD')
    header_style.set_align('center')
    header_style.set_color("white")
    worksheet = workbook.add_worksheet(u"2018新B端凹凸明细数据")
    worksheet.set_column('A:Z', 12)
    worksheet.set_column('A:Z', 12)
    worksheet.set_column('U:V', 20)
    worksheet.set_column('W:X', 40)
    header = ("城市编号","城市名称","主大客户名","大客户名","用户ID","主号","子号","联系电话","联主号名称","订单编号","下单日期","报单日期","司机编号","车牌","订单金额","等待费","里程费","信息费","等待时长","里程","开始代驾时间","结束代驾时间"
              ,"开始代驾地址","结束代驾地址","特殊字段","客服备注")
    for x in range(len(header)) :
        worksheet.write(0, x, header[x],header_style)

    cur.execute(SQL.replace("#end_date#",day).replace("#begin_date#",str(int(day)-1)).replace("#customer#","凹凸租车"))
    row_index = 1
    col_index = 0
    for i in cur:
        col_index = 0
        for ii in i:
            worksheet.write(row_index,col_index,ii)
            col_index += 1
        row_index += 1

    workbook.close()

    #万科无锡常州
    wanke_file_name = u'D:\工作相关\大客户数据\万科无锡常州\万科无锡常州%s.xlsx' % day
    workbook = xlsxwriter.Workbook(wanke_file_name)
    header_style = workbook.add_format({'bold': True})
    header_style.set_bg_color('#4F81BD')
    header_style.set_align('center')
    header_style.set_color("white")
    worksheet = workbook.add_worksheet(u"2018新B端万科无锡常州")
    worksheet.set_column('A:Z', 12)
    worksheet.set_column('A:Z', 12)
    worksheet.set_column('U:V', 20)
    worksheet.set_column('W:X', 40)
    worksheet
    header = ("城市编号","城市名称","主大客户名","大客户名","用户ID","主号","子号","联系电话","联主号名称","订单编号","下单日期","报单日期","司机编号","车牌","订单金额","等待费","里程费","信息费","等待时长","里程","开始代驾时间","结束代驾时间"
              ,"开始代驾地址","结束代驾地址","特殊字段","客服备注")
    for x in range(len(header)) :
        worksheet.write(0, x, header[x],header_style)

    cur.execute(SQL.replace("#end_date#",day).replace("#begin_date#",str(int(day)-1)).replace("#customer#","万科无锡常州"))
    row_index = 1
    col_index = 0
    for i in cur:
        col_index = 0
        for ii in i:
            worksheet.write(row_index,col_index,ii)
            col_index += 1
        row_index += 1

    workbook.close()

    msg = MIMEMultipart()
    title = u'%s 的凹凸租车、万科无锡常州每日数据'% day
    #接收者
    msg['to'] = 'wangyifei@aidaijia.com'
    msg['from'] = 'syssend@aidaijia.com'
    msg['Subject'] = title
    text_msg = MIMEText.MIMEText(u'Hi 你好:\n %s 的凹凸租车、万科无锡常州每日数据在附件中了,请查收，谢谢。 ' % (str(int(day)-1)), _charset='gbk')
    msg.attach(text_msg)
    # part = MIMEApplication(open(file_name, 'rb').read())
    # part.add_header('Content-Disposition', 'attachment', filename=file_name.replace('D:\工作相关\大客户数据\\',"").encode('gbk'))
    # part = MIMEApplication(open(wanke_file_name, 'rb').read())
    # part.add_header('Content-Disposition', 'attachment', filename=wanke_file_name.replace('D:\工作相关\大客户数据\万科无锡常州\\',"").encode('gbk'))
    # msg.attach(part)
    attachment_file_name = file_name+","+wanke_file_name
    for tmp_attachment_file_name in attachment_file_name.split(","):
        contype = 'application/octet-stream'
        maintype, subtype = contype.split('/', 1)
        file_data = open(tmp_attachment_file_name.encode('gbk'),'rb')
        file_msg = email.MIMEBase.MIMEBase(maintype, subtype)
        file_msg.set_payload(file_data.read())
        file_data.close( )
        email.Encoders.encode_base64(file_msg)
        basename = os.path.basename(tmp_attachment_file_name)
        file_msg.add_header('Content-Disposition', 'attachment', filename = basename.encode("gbk"))
        msg.attach(file_msg)
    try:
        server = smtplib.SMTP()
        server.connect('smtp.exmail.qq.com')
        #邮箱账户和密码
        server.login('syssend@aidaijia.com', 'sys.faxin.123')
        server.sendmail(msg['from'], msg['to'].split(','), msg.as_string())
        server.quit()
    except Exception, e:
        print str(e)
